﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Gencode.Common.SQL;
using Gencode.PrsCommon.App;

namespace Gencode.PrsCommon.Data.Person
{
	public enum PersonTyp { Alle, Stammperson, Zusatzperson };

	public class PersonData
	{
		/// <summary>
		/// Alle Personen. 
		/// [prsid,vorname,nachname,strasse,stnr,postfach,plz,ort,telp,teln,email,anzeigename]
		/// </summary>
		/// <param name="mandid">Mandant</param>
		/// <returns></returns>
		public static DataView GetPersonen(string mandid)
		{
			return GetPersonen(mandid, 0, PersonTyp.Alle);
		}

		/// <summary>
		/// Alle Personen des Typs. 
		/// [prsid,vorname,nachname,strasse,stnr,postfach,plz,ort,telp,teln,email,anzeigename]
		/// </summary>
		/// <param name="mandid">Manant</param>
		/// <param name="typ">PersonTyp</param>
		/// <returns></returns>
		public static DataView GetPersonen(string mandid, PersonTyp typ)
		{
			return GetPersonen(mandid, 0, typ);
		}

		/// <summary>
		/// Alle Personen des Typs with a empt row at top. 
		/// [prsid,vorname,nachname,strasse,stnr,postfach,plz,ort,telp,teln,email,anzeigename]
		/// </summary>
		/// <param name="mandid">Manant</param>
		/// <param name="typ">PersonTyp</param>
		/// <param name="addEmptyRow">add empty row at top?</param>
		/// <returns></returns>
		public static DataView GetPersonen(string mandid, PersonTyp typ, bool addEmptyRow)
		{
			DataView dv = GetPersonen(mandid, 0, typ);

			if (addEmptyRow)
			{
				DataRow row = dv.Table.NewRow();
				dv.Table.Rows.InsertAt(row, 0);
			}

			return dv;
		}

		/// <summary>
		/// [prsid,vorname,nachname,strasse,stnr,postfach,plz,ort,telp,teln,email,anzeigename]
		/// </summary>
		/// <param name="mandid">Mandant</param>
		/// <param name="prsID">Person ID</param>
		/// <returns></returns>
		public static DataView GetPerson(string mandid, int prsID)
		{
			return GetPersonen(mandid, prsID, PersonTyp.Alle);
		}

		/// <summary>
		/// [prsid,vorname,nachname,strasse,stnr,postfach,plz,ort,telp,teln,email,anzeigename]
		/// </summary>
		/// <param name="mandid"></param>
		/// <param name="prsID"></param>
		/// <param name="typ"></param>
		/// <returns></returns>
		private static DataView GetPersonen(string mandid, int prsID, PersonTyp typ)
		{
			int t = 0;
			if (typ == PersonTyp.Stammperson)
				t = 1;
			else if (typ == PersonTyp.Zusatzperson)
				t = 2;
			else if (typ == PersonTyp.Alle)
				t = 0;

			Querry query = new Querry();
			query.SetSTPName("get_personen");
			query.AddParameter("@mandid", mandid);
			query.AddParameter("@prsid", prsID);
			query.AddParameter("@typ", t);

			return query.ExecuteForDataView();
		}

		/// <summary>
		/// Creates a DataView with the Pensum of a Person with stichdatum today. prsid,von,bis,pensum 
		/// </summary>
		/// <param name="prsid">prsid</param>
		/// <returns>prsid,von,bis,pensum </returns>
		public static DataView GetGesamtPensum(int prsid)
		{
			return GetGesamtPensum(prsid, DateTime.Now.ToShortDateString());
		}

		/// <summary>
		/// Creates a DataView with the Pensum of a Person at stichdatum. prsid,von,bis,pensum 
		/// </summary>
		/// <param name="prsid">prsid</param>
		/// <param name="stichdatum">stichdatum</param>
		/// <returns>prsid,von,bis,pensum </returns>
		public static DataView GetGesamtPensum(int prsid, string stichdatum)
		{
			Querry query = new Querry();
			query.SetSTPName("get_personpensum");
			query.AddParameter("@prsid", prsid);
			query.AddParameter("@stichdatum", DateUtil.ConvertDateToSQL(stichdatum));
			return query.ExecuteForDataView();
		}

		public static int GetArbeitspensum(int prsid)
		{
			DataView dv = GetGesamtPensum(prsid);
			if (dv.Count > 0)
				return int.Parse(dv[0].Row["pensum"].ToString());
			return 100;
		}

		public static int GetArbeitspensum(int prsid, string stichdatum)
		{
			DataView dv = GetGesamtPensum(prsid, stichdatum);
			if (dv.Count > 0)
				return int.Parse(dv[0].Row["pensum"].ToString());
			return 100;
		}

		/// <summary>
		/// Creates a DataView with a specific abteilung from a Person. abteilungid,pensum,von,bis,name
		/// </summary>
		/// <param name="prsid">prsid</param>
		/// <param name="abteilungid">abteilungid</param>
		/// <returns>abteilungid,pensum,von,bis,name</returns>
		public static DataView GetPersonAbteilung(int prsid, int abteilungid)
		{
			Querry query = new Querry();
			query.SetSTPName("get_arbeitspensum");
			query.AddParameter("@prsid", prsid);
			query.AddParameter("@abteilungid", abteilungid);
			return query.ExecuteForDataView();
		}

		/// <summary>
		/// Creates a DataView with all abteilungen of a Person. abteilungid,pensum,von,bis,name
		/// </summary>
		/// <param name="prsid">PrsID</param>
		/// <returns>abteilungid,pensum,von,bis</returns>
		public static DataView GetPersonAbteilung(int prsid)
		{
			return GetPersonAbteilung(prsid, 0);
		}


		/// <summary>
		/// Saves a person. [prsid]
		/// </summary>
		/// <param name="vorname"></param>
		/// <param name="nachname"></param>
		/// <param name="strasse"></param>
		/// <param name="strassennr"></param>
		/// <param name="postfach"></param>
		/// <param name="plz"></param>
		/// <param name="ort"></param>
		/// <param name="telefonp"></param>
		/// <param name="natel"></param>
		/// <param name="email"></param>
		/// <param name="anstellungdatumvon">normal datetime</param>
		/// <param name="anstellungdatumbis">normal datetime</param>
		/// <param name="pensum"></param>
		/// <param name="anstellungid"></param>
		/// <param name="ptyp"></param>
		/// <returns>prsid</returns>
		public static int SavePerson(string vorname, string nachname, string strasse, string strassennr,
			string postfach, string plz, string ort, string telefonp,
			string natel, string email, string anstellungdatumvon, string anstellungdatumbis,
			int pensum, int anstellungid, PersonTyp ptyp, string mandid)
		{
			if (ptyp == PersonTyp.Alle)
				ptyp = PersonTyp.Stammperson;

			Querry query = new Querry();
			query.SetSTPName("set_person");

			query.AddParameter("@mandid", mandid);
			query.AddParameter("@vorname", vorname);
			query.AddParameter("@nachname", nachname);
			query.AddParameter("@strasse", strasse);
			query.AddParameter("@strassennr", strassennr);
			query.AddParameter("@postfach", postfach);
			query.AddParameter("@plz", plz);
			query.AddParameter("@ort", ort);
			query.AddParameter("@telnrp", telefonp);
			query.AddParameter("@telnrn", natel);
			query.AddParameter("@email", email);
			query.AddParameter("@pensumvon", DateUtil.ConvertDateToSQL(anstellungdatumvon));
			query.AddParameter("@pensumbis", DateUtil.ConvertDateToSQL(anstellungdatumbis));
			query.AddParameter("@pensumprozent", pensum);
			query.AddParameter("@anstellungid", anstellungid);
			query.AddParameter("@persontyp", (int)ptyp);

			int prsid = 0;
			DataView dv = query.ExecuteForDataView(true);
			if (dv.Count > 0)
				try
				{
					prsid = int.Parse(dv[0].Row[0].ToString());
				}
				catch { }
			dv.Dispose();

			return prsid;

		}

		/// <summary>
		/// Updates a person [prsid]
		/// </summary>
		/// <param name="prsid"></param>
		/// <param name="vorname"></param>
		/// <param name="nachname"></param>
		/// <param name="strasse"></param>
		/// <param name="strassennr"></param>
		/// <param name="postfach"></param>
		/// <param name="plz"></param>
		/// <param name="ort"></param>
		/// <param name="telefonp"></param>
		/// <param name="natel"></param>
		/// <param name="email"></param>
		/// <param name="anstellungdatumvon"></param>
		/// <param name="anstellungdatumbis"></param>
		/// <param name="pensum"></param>
		/// <param name="anstellungid"></param>
		/// <param name="ptyp"></param>
		/// <returns>prsid</returns>
		public static int UpdatePerson(int prsid, string vorname, string nachname, string strasse, string strassennr,
			string postfach, string plz, string ort, string telefonp,
			string natel, string email, string anstellungdatumvon, string anstellungdatumbis,
			int pensum, int anstellungid)
		{
			Querry query = new Querry();
			query.SetSTPName("upd_Person");

			query.AddParameter("@vorname", vorname);
			query.AddParameter("@nachname", nachname);
			query.AddParameter("@strasse", strasse);
			query.AddParameter("@strassennr", strassennr);
			query.AddParameter("@postfach", postfach);
			query.AddParameter("@plz", plz);
			query.AddParameter("@ort", ort);
			query.AddParameter("@telnrp", telefonp);
			query.AddParameter("@telnrn", natel);
			query.AddParameter("@email", email);
			query.AddParameter("@pensumvon", DateUtil.ConvertDateToSQL(anstellungdatumvon));
			query.AddParameter("@pensumbis", DateUtil.ConvertDateToSQL(anstellungdatumbis));
			query.AddParameter("@pensumprozent", pensum);
			query.AddParameter("@anstellungid", anstellungid);
			query.AddParameter("@prsid", prsid);

			DataView dv = query.ExecuteForDataView(true);
			if (dv.Count > 0)
				prsid = int.Parse(dv[0].Row[0].ToString());
			dv.Dispose();

			return prsid;

		}

		public static void SetAbteilung(int prsid, int abteilungid, string von, string bis, int prozent)
		{
			Querry stp = new Querry();
			stp.SetSTPName("set_prsabteilung");

			stp.AddParameter("@abteilungid", abteilungid);
			stp.AddParameter("@prsid", prsid);
			stp.AddParameter("@von", DateUtil.ConvertDateToSQL(von));
			stp.AddParameter("@bis", DateUtil.ConvertDateToSQL(bis));
			stp.AddParameter("@prozent", prozent);

			stp.Execute(true);
		}

		public static void RemovePersonAbteilung(int prsid, int abteilungid, string datevon, string datebis)
		{
			Querry stp = new Querry();
			stp.SetSTPName("upd_prsabteilung");

			stp.AddParameter("@abteilungid", abteilungid);
			stp.AddParameter("@prsid", prsid);
			stp.AddParameter("@vonnew", DateUtil.ConvertDateToSQL(datevon));
			stp.AddParameter("@bisnew", DateUtil.ConvertDateToSQL(datebis));

			stp.Execute(true);
		}

		public static void SetAnstellungToPerson(int prsid, int anstellungid, string von, string bis)
		{
			Querry stp = new Querry();
			stp.SetSTPName("upd_prsanstellung");
			stp.AddParameter("@prsid", prsid);
			stp.AddParameter("@anstellungid", anstellungid);
			stp.AddParameter("@von", DateUtil.ConvertDateToSQL(von));
			stp.AddParameter("@bis", DateUtil.ConvertDateToSQL(bis));

			stp.Execute(true);
		}

		public static bool SetPersonGesamtPensum(int prsid, int pensum, string von, string bis)
		{
            Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, "Pensum ändern von Person:" + prsid + " Prozent:" + pensum + " Von:" + von + " Bis:" + bis);
			
			Querry stp = new Querry("set_newpensum");

			stp.AddParameter("@prsid", prsid);
			stp.AddParameter("@pensum", pensum);
			stp.AddParameter("@von", DateUtil.ConvertDateToSQL(von));
			stp.AddParameter("@bis", DateUtil.ConvertDateToSQL(bis));

			return stp.Execute(true);
		}

		public static bool PersonPensumKuendigung(int prsid, string datum)
		{
            Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, "Kündigung von Person:" + prsid + " Am:" + datum);

			Querry stp = new Querry("set_personkuendigung");

			stp.AddParameter("@prsid", prsid);
			stp.AddParameter("@bis", DateUtil.ConvertDateToSQL(datum));

			return stp.Execute(true);
		}

		public static DataView GetFerien(int prsid, int year)
		{
			return SimpleQuerry.ExecuteForDataView("select FerienID,PrsId,Von,Bis from Ferien where PrsId = " + prsid + " and (DATEPART(YYYY,Von) = " + year + " or DATEPART(YYYY,Bis) = " + year + ") order by Von,Bis");
		}

		public static void DeleteFerien(int ferienid)
		{
			SimpleQuerry.Execute("delete from Ferien where FerienID = " + ferienid);
		}

		/// <summary>
		/// [PrsId,AbteilungID,Name,Von,Bis,Prozent,Status,Statustext]
		/// </summary>
		/// <param name="prsid"></param>
		/// <returns></returns>
		public static DataView GetPersonAbteilungHistory(int prsid)
		{
			return SimpleQuerry.ExecuteForDataView("select PrsId,a.AbteilungID,a.Name,Von,Bis,Prozent,Status,Statustext=case when Status=1 then 'Aktiv' else 'Inaktiv'end from PersonalAbteilung pa inner join Abteilung a on pa.AbteilungID=a.AbteilungID where PrsId = " + prsid + " order by Von desc, Bis");
		}

		/// <summary>
		/// [PrsID,Von,Bis,Prozent,Status,Statustext]
		/// </summary>
		/// <param name="prsid"></param>
		/// <returns></returns>
		public static DataView GetPersonArbeitspensumHistory(int prsid)
		{
			return SimpleQuerry.ExecuteForDataView("select PrsID,Von,Bis,Prozent,Status,Statustext=case when Status=1 then 'Aktiv' else 'Inaktiv'end from Arbeitspensum where PrsId = " + prsid);
		}
	}
}
